package net.sky.oa.dao.post.impl;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sky.oa.dao.SkyBaseDaoImpl;
import net.sky.oa.dao.post.SendPostDao;
import net.sky.oa.model.SendPost;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

@SuppressWarnings("unchecked")
@Repository
public class SendPostDaoImpl extends SkyBaseDaoImpl<SendPost> implements SendPostDao{
    
	public SendPostDaoImpl(){
		super.setPersistType(SendPost.class);
	}
	
	public List<SendPost> findAllSendPost() {
		String hql="from SendPost sp";
		return findByHql(hql);
	}
	//最新发帖
    public List<SendPost> newPostList(){
    	List list=getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				  String hql="select new SendPost(sp.id,sp.title,sp.createtime,sp.user) from  SendPost sp " +
				  		"order by sp.createtime desc";
				  Query query=session.createQuery(hql);
				  query.setFirstResult(0);
				  query.setMaxResults(5);
				  return query.list();
			}
    	});
    	return list;
    }
    //最高人气
    public List<SendPost> popularityList(){
    	List list=getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				    String hql="select new SendPost(sp.id,sp.title) from SendPost sp " +
						"order by sp.peoplecount desc";
				  Query query=session.createQuery(hql);
				  query.setFirstResult(0);
				  query.setMaxResults(5);
				  return query.list();
			}
    	});
    	return list;
    }
    //最新回复
    public List<SendPost> newReplyList(){
    	List list=getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String hql="select new SendPost(sp.id,sp.title) from SendPost sp where sp.lastreply is not null order by " +
						"to_date(substr(sp.lastreply,0,19),'yyyy-MM-dd hh24:mi:ss') desc ";
				  Query query=session.createQuery(hql);
				  query.setFirstResult(0);
				  query.setMaxResults(5);
				  return query.list();
			}
    	});
    	return list;
    }
	public Map findSendPostOptions(final String  title,final int type,final String postuser,final int currentPage,final int lineSize) throws Exception{
		Map map=new HashMap();
	    List list=getHibernateTemplate().executeFind(new HibernateCallback(){

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuffer buffer=new StringBuffer("select new SendPost(sp.id,sp.type,sp.title,sp.peoplecount,sp.isreply,sp.user,sp.lastreply) from SendPost sp where 1=1 ");
				StringBuffer cbuffer=new StringBuffer("select count(sp.id) from SendPost sp where 1=1");
				String hql="";
				if(title!=null && !"".equals(title)){
					 hql=" and sp.title like '%"+title+"%'";
					 buffer.append(hql);
					 cbuffer.append(hql);
				}
				if(type!=-1){
					 hql=" and sp.type="+type;
					 buffer.append(hql);
					 cbuffer.append(hql);
				}
				if(postuser!=null && !"".equals(postuser)){
					 hql=" and sp.user.name like '%"+postuser+"%'";
					 buffer.append(hql);
					 cbuffer.append(hql);
				}
				 buffer.append(" order by " +
				 		"case " +
				 		" when sp.createtime > to_date(substr(sp.lastreply,0,19),'yyyy-MM-dd hh24:mi:ss') " +
				 		" then sp.createtime  " +
				 		" when sp.createtime < to_date(substr(sp.lastreply,0,19),'yyyy-MM-dd hh24:mi:ss') " +
				 		" then to_date(substr(sp.lastreply,0,19),'yyyy-MM-dd hh24:mi:ss') " +
				 		" when to_date(substr(sp.lastreply,0,19),'yyyy-MM-dd hh24:mi:ss') is null" +
				 		" then sp.createtime " +
				 		" when sysdate > to_date(substr(sp.lastreply,0,19),'yyyy-MM-dd hh24:mi:ss') " +
				 		" then sp.createtime end desc nulls last");
				 List resultList = new ArrayList();
				 Long count = (long)0;
				 Query query = session.createQuery(buffer.toString());
				 if(currentPage!=-1&&lineSize!=-1) {
			     count =(Long)session.createQuery(cbuffer.toString()).uniqueResult();
			     resultList = query.setFirstResult((currentPage - 1) * lineSize)
					.setMaxResults(lineSize).list();
				 }else {
						resultList = query.list();
				 }
				    List list = new ArrayList();
					list.add(count);
					list.add(resultList);				
				    return list;
			}
	    });
	            map.put("count",list.get(0) );
	            map.put("list", list.get(1) );
		        return map;
	}
	
}
